Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Collections.Generic
Imports Microsoft.Practices.EnterpriseLibrary.Data

Namespace DAL.Concesion
    Public Class Postor
        Public Function Insertar(ByVal _Postor As BEL.Concesion.Juridica) As System.Boolean
            Dim db As Database = DatabaseFactory.CreateDatabase("ServCons")
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_I_Persona]")
            Dim cmJur As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_I_Juridico]")

            Using cn As DbConnection = db.CreateConnection()
                cn.Open()
                Dim tx As DbTransaction = cn.BeginTransaction()

                Try
                    'Insertamos datos de persona
                    db.AddInParameter(cm, "NO_NOMBRE1", DbType.String, _Postor.NO_NOMBRE1)
                    db.AddInParameter(cm, "NO_NOMBRE2", DbType.String, _Postor.NO_NOMBRE2)
                    db.AddInParameter(cm, "TX_DIRECCION", DbType.String, _Postor.TX_DIRECCION)
                    db.AddInParameter(cm, "TX_TELEFONO", DbType.String, _Postor.TX_TELEFONO)
                    db.AddInParameter(cm, "TX_EMAIL", DbType.String, _Postor.TX_EMAIL)

                    Using dr As IDataReader = db.ExecuteReader(cm, tx)
                        dr.Read()
                        _Postor.CO_PERSONA = Convert.ToInt16(dr("CO_PERSONA"))
                    End Using

                    'Insertamos datos de Juridico
                    db.AddInParameter(cmJur, "NO_RUC", DbType.String, _Postor.NO_RUC)
                    db.AddInParameter(cmJur, "TX_CONTACTO", DbType.String, _Postor.TX_CONTACTO)
                    db.AddInParameter(cmJur, "CO_PERSONA", DbType.Int16, _Postor.CO_PERSONA)

                    Using drJur As IDataReader = db.ExecuteReader(cmJur, tx)
                        drJur.Read()
                        _Postor.CO_JURIDICO = Convert.ToInt16(drJur("CO_JURIDICO"))
                    End Using

                    tx.Commit()
                Catch _ex As Exception
                    tx.Rollback()
                    Throw New Exception(_ex.Message, _ex.InnerException)
                Finally
                    tx.Dispose()
                    cm.Dispose()
                    tx = Nothing
                    cm = Nothing
                    db = Nothing
                End Try
            End Using
            Return True
        End Function
        Public Function Modificar(ByVal _Postor As BEL.Concesion.Juridica) As System.Boolean
            Dim db As Database = DatabaseFactory.CreateDatabase("ServCons")
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_U_Persona]")
            Dim cmPer As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_Obtiene_Persona]")
            Dim cmJur As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_U_Juridico]")

            Using cn As DbConnection = db.CreateConnection()
                cn.Open()
                Dim tx As DbTransaction = cn.BeginTransaction()

                Try
                    'Obtenemos el codigo de persona asociado al de juridico
                    db.AddInParameter(cmPer, "CO_JURIDICO", DbType.Int16, Convert.ToInt16(_Postor.CO_JURIDICO))
                    Using dr As IDataReader = db.ExecuteReader(cmPer, tx)
                        dr.Read()
                        _Postor.CO_PERSONA = Convert.ToInt16(dr("CO_PERSONA"))
                    End Using


                    'Insertamos datos de persona
                    db.AddInParameter(cm, "CO_PERSONA", DbType.Int16, Convert.ToInt16(_Postor.CO_PERSONA))
                    db.AddInParameter(cm, "NO_NOMBRE1", DbType.String, _Postor.NO_NOMBRE1)
                    db.AddInParameter(cm, "NO_NOMBRE2", DbType.String, _Postor.NO_NOMBRE2)
                    db.AddInParameter(cm, "TX_DIRECCION", DbType.String, _Postor.TX_DIRECCION)
                    db.AddInParameter(cm, "TX_TELEFONO", DbType.String, _Postor.TX_TELEFONO)
                    db.AddInParameter(cm, "TX_EMAIL", DbType.String, _Postor.TX_EMAIL)
                    db.ExecuteNonQuery(cm, tx)


                    'Insertamos datos de Juridico
                    db.AddInParameter(cmJur, "CO_JURIDICO", DbType.Int16, _Postor.CO_JURIDICO)
                    db.AddInParameter(cmJur, "NO_RUC", DbType.String, _Postor.NO_RUC)
                    db.AddInParameter(cmJur, "TX_CONTACTO", DbType.String, _Postor.TX_CONTACTO)
                    db.ExecuteNonQuery(cmJur, tx)

                    tx.Commit()
                Catch _ex As Exception
                    tx.Rollback()
                    Throw New Exception(_ex.Message, _ex.InnerException)
                Finally
                    tx.Dispose()
                    cm.Dispose()
                    cmPer.Dispose()
                    cmJur.Dispose()
                    tx = Nothing
                    cm = Nothing
                    cmPer = Nothing
                    cmJur = Nothing
                    db = Nothing
                End Try
            End Using
            Return True
        End Function

        Public Function Listar() As DataTable
            Dim ds As New DataSet()
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_Lista_Juridico]")
            Try
                ds = db.ExecuteDataSet(cm)
                ds.Tables(0).TableName = "POSTORES"
                Return ds.Tables(0)
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
        End Function
        Public Function ListarCombo() As DataTable
            Dim ds As New DataSet()
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_Juridica_Listado]")
            Try
                ds = db.ExecuteDataSet(cm)
                ds.Tables(0).TableName = "POSTORES"
                Return ds.Tables(0)
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
        End Function

        Public Function Buscar(ByVal _Parametros As Object()) As DataTable
            Dim ds As New DataSet()
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_Busca_Juridico]")
            db.AddInParameter(cm, "NO_NOMBRE1", DbType.String, Convert.ToString(_Parametros(0)))
            db.AddInParameter(cm, "NO_RUC", DbType.String, Convert.ToString(_Parametros(1)))
            Try
                ds = db.ExecuteDataSet(cm)
                ds.Tables(0).TableName = "POSTORES"
                Return ds.Tables(0)
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
        End Function
        Public Function Eliminar(ByVal intId As Int32) As System.Boolean
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_Obtiene_Persona]")
            Dim cmPer As DbCommand = db.GetStoredProcCommand("[SER_D_Persona]")
            Dim cmJur As DbCommand = db.GetStoredProcCommand("[SER_D_Juridico]")
            Dim intPersona As Int32

            Using cn As DbConnection = db.CreateConnection()
                cn.Open()
                Dim tx As DbTransaction = cn.BeginTransaction()

                Try
                    'Obtenemos el codigo de persona asociado al de juridico
                    db.AddInParameter(cm, "CO_JURIDICO", DbType.Int16, Convert.ToInt16(intId))
                    Using dr As IDataReader = db.ExecuteReader(cm, tx)
                        dr.Read()
                        intPersona = Convert.ToInt16(dr("CO_PERSONA"))
                    End Using

                    Dim intRpta As Integer
                    'Eliminamos datos de juridico
                    db.AddInParameter(cmJur, "CO_JURIDICO", DbType.Int16, intId)
                    Using dr As IDataReader = db.ExecuteReader(cmJur, tx)
                        dr.Read()
                        intRpta = Convert.ToInt32(dr("ROWCOUNT"))
                    End Using

                    If intRpta > 0 Then
                        'Eliminados datos de persona
                        db.AddInParameter(cmPer, "CO_PERSONA", DbType.Int16, intPersona)
                        Using dr As IDataReader = db.ExecuteReader(cmPer, tx)
                            dr.Read()
                            intRpta = Convert.ToInt32(dr("ROWCOUNT"))
                        End Using
                    End If
                    tx.Commit()

                    If intRpta > 0 Then
                        Return True
                    Else
                        Return False
                    End If
                Catch _ex As Exception
                    tx.Rollback()
                    Throw New Exception(_ex.Message, _ex.InnerException)
                Finally
                    tx.Dispose()
                    cm.Dispose()
                    cmPer.Dispose()
                    cmJur.Dispose()
                    tx = Nothing
                    cm = Nothing
                    cmPer = Nothing
                    cmJur = Nothing
                    db = Nothing
                End Try
            End Using
            Return True
        End Function
	End Class
End Namespace